package com.duan.common.excel;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.format.CellDateFormatter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.Assert;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * Excel 帮助类
 * 功能：
 *  1. 解析Excel文件
 *  2. 写入Excel
 *  3. Excel文件写入压缩包
 *
 * @author duan
 * @date 2020-11-17
 */
public class ExcelHelper {

    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    /**
     * 解析Excel文件
     * @param inputStream excel文件流
     * @param fileName excel文件名称，带后缀，根据后缀判断文件类型
     * @return 返回excel数据（多sheet）
     * @throws IOException 创建excel工作簿时找不到文件会抛出该异常
     */
    public static List<List<Map<String, Object>>> parserExcel(InputStream inputStream, String fileName) throws IOException {
        Assert.notNull(fileName, "文件格式不能为空");
        Workbook workbook;
        if (fileName.toLowerCase().endsWith(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileName.toLowerCase().endsWith(XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        } else {
            throw new IllegalArgumentException("The format is not supported");
        }
        Assert.notNull(workbook, "创建Excel工作簿失败");
        return parseExcel(workbook);
    }

    /**
     * 读取Excel内容
     * @param workbook excel工作簿
     * @return 返回excel数据，一个map对象一行数据，一个List<Map>对应一个sheet，List<List<Map>> 表示所有sheet数据</>
     */
    private static List<List<Map<String, Object>>> parseExcel(Workbook workbook) {
        List<List<Map<String, Object>>> lists = new LinkedList<>();
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);
            if (sheet == null) {
                continue;
            }
            List<Map<String, Object>> list = new LinkedList<>();
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum ++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }
                Map<String, Object> map = new HashMap<>(row.getLastCellNum());
                for (int colNum = 0; colNum < row.getLastCellNum(); colNum++) {
                    Cell cell = row.getCell(colNum);
                    String key = String.format("column%d", colNum);
                    map.put(key, getCellValue(cell));
                }
                list.add(map);
            }
            lists.add(list);
        }
        return lists;
    }

    /**
     * 读取单元格数据
     * @param cell 单元格
     * @return 返回单元格数据
     */
    private static Object getCellValue(Cell cell) {
        CellType cellType = cell.getCellType();
        if (cellType.equals(CellType.FORMULA)) {
            cellType = cell.getCachedFormulaResultType();
        }
        switch (cellType) {
            case _NONE:
            case BLANK:
            case ERROR:
                return "";
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    String format = cell.getCellStyle().getDataFormatString();
                    // format总是以“;@”结尾，具体没看明白，所以这里需要处理一下
                    format = format.substring(0, format.length() - 2);
                    Date date = cell.getDateCellValue();
                    return new CellDateFormatter(format).format(date);
                }
                return cell.getNumericCellValue();
        }
        return "";
    }
}
